﻿Imports System.Data


Public Class cls_connection

    Public strConnection As String

    Public Cnt As Odbc.OdbcConnection
    Private Cmd As Odbc.OdbcCommand
    Public Read As Odbc.OdbcDataReader

    Private DA As New Odbc.OdbcDataAdapter
    Private DS As DataSet
    Private DT As DataTable

    'Public Sub initConnection(ByVal StringConnection As String)
    '    strConnection = StringConnection
    'End Sub

    Public Function openConnection() As Boolean
        strConnection = "Driver={MySQL ODBC 5.1 Driver}" & _
                        ";Server=" & APP.Info.Host & _
                        ";Port=" & APP.Info.Port & _
                        ";Database=" & APP.Info.Database & _
                        ";User=" & APP.Info.User & _
                        ";Password=" & APP.Info.Password & ";"

        Try
            Cnt = New Odbc.OdbcConnection(strConnection)
            If Cnt.State <> ConnectionState.Closed Then
                Cnt.Close()
            End If
            Cnt.Open()
            Return True
        Catch ex As Exception
            MsgBox("openConnection Failed...!!" & vbCrLf & ex.Message, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Access Failed")
            Return False
        End Try

    End Function

    Public Sub closeConnection()
        If Not IsNothing(Cnt) Then
            Cnt.Close()
            Cnt = Nothing
        End If
    End Sub

    Public Function ExecuteToDataTable(ByVal Query As String) As DataTable

        If openConnection() Then
            Try
                Cmd = New Odbc.OdbcCommand(Query, Me.Cnt)
                DA = New Odbc.OdbcDataAdapter
                DA.SelectCommand = cmd

                DS = New DataSet
                DA.Fill(DS)

                DT = DS.Tables(0)

                ' return
                ExecuteToDataTable = DT
            Catch ex As Exception
                MsgBox("ExecuteToDataTable Failed...!!" & vbCrLf & ex.Message, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Access Failed")
                ExecuteToDataTable = Nothing
            End Try
        Else
            ExecuteToDataTable = Nothing
        End If

        DT = Nothing
        DS = Nothing
        DA = Nothing
        cmd = Nothing

        closeConnection()

    End Function

    Public Function ExecuteToDataReader(ByVal Query As String) As Odbc.OdbcDataReader

        If openConnection() Then
            Try

                Cmd = New Odbc.OdbcCommand(Query, Me.Cnt)
                Read = Cmd.ExecuteReader

                ExecuteToDataReader = Read

            Catch ex As Exception
                MsgBox("ExecuteToDataReader Failed...!!" & vbCrLf & ex.Message, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Access Failed")
                ExecuteToDataReader = Nothing
            End Try
        Else
            ExecuteToDataReader = Nothing
        End If

        Cmd = Nothing

        'closeConnection()

    End Function

    Public Function ExecuteNonQuery(ByVal Query As String) As Boolean
        If openConnection() Then
            Try

                Cmd = New Odbc.OdbcCommand(Query, Me.Cnt)
                Cmd.ExecuteNonQuery()
                ExecuteNonQuery = True

            Catch ex As Exception
                MsgBox("ExecuteNonQuery Failed...!!" & vbCrLf & ex.Message, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Access Failed")
                ExecuteNonQuery = False
            End Try
        Else
            ExecuteNonQuery = False
        End If

        Cmd = Nothing
        closeConnection()
    End Function

End Class
